package com.ja10.books;

import java.io.IOException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import com.example.books.R;

import android.annotation.SuppressLint;
import android.app.Activity;
import android.content.ContentValues;
import android.content.Intent;
import android.content.SharedPreferences;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.ListView;
import android.widget.TextView;

public class RentalActivity extends Activity {

	SimpleDatabaseHelper helper = null;
	SQLiteDatabase db = null;
	SearchListAdapter<RentalList> adapter = null;
	ArrayList<RentalList> data = null;
	RentalList item = null;
	ListView list = null;
	Intent i = null;
	
	SharedPreferences settings = null;
	TextView lastUpDate;
	
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.activity_rental);
		
		settings = getSharedPreferences(LoginActivity.PREFERENCES_FILE_NAME, Activity.MODE_PRIVATE);
		lastUpDate = (TextView)findViewById(R.id.txtLastupdate);
		lastUpDate.setText(settings.getString("lastUpDate", ""));
		
		listShow();
	}
	
	@SuppressLint({ "CommitPrefEdits", "DefaultLocale" })
	public void listShow() {
		// ヘルパーを準備
		helper = new SimpleDatabaseHelper(this);
		
		// 端末データベースの取得
		db = helper.getReadableDatabase();
		Cursor cs = db.query("rentals", null, null, null, null, null, "id desc");
		
		// 配列の内容をListオブジェクトに詰め替え
		data = new ArrayList<RentalList>();
		
		boolean eol = cs.moveToFirst();
		while(eol) {
			RentalList item = new RentalList();
			item.setId(cs.getInt(0));
			item.setTitle(cs.getString(1));
			item.setWriter(cs.getString(2));
			item.setRentalDate(cs.getString(3));
			if(cs.getBlob(4) != null) {
				item.setPictureBmp(cs.getBlob(4));
			}
			item.setReturnPlanDate(cs.getString(5));
			item.setIsbn(cs.getString(6));
			
			data.add(item);
			eol = cs.moveToNext();
		}
		cs.close();
		db.close();

		// アダプターをもとにリストを生成
		adapter = new SearchListAdapter<RentalList>(this, data, R.layout.list_item_book, 2);
        ListView listView = (ListView) findViewById(R.id.listBookReturn);
        listView.setAdapter(adapter);
        
	}

	public void btnLogin_onClick(View view){
		
	// LoginActivityへのインテントを作成
		Intent i = new Intent(this, LoginActivity.class);
	// アクティビティを起動
		startActivity(i);
	}		
	
	public void btnMenu_onClick(View view){
		
//		// SearchResultActivityへのインテントを作成
			Intent i = new Intent(this, MainActivity.class);
//		// アクティビティを起動
			startActivity(i);
			
		// SearchResultActivityを終了
			finish();
	}	
	
	public void btnRefresh_onClick(View v) throws SQLException{
			
		Connection con = null;
		Statement smt = null;
			
		helper = new SimpleDatabaseHelper(this);
		SQLiteDatabase db = helper.getReadableDatabase();
		
//		db.execSQL("delete from rentals");
		
		// 端末内レンタルテーブルより書籍ISBNの取得
		Cursor cs = db.query("rentals", null, null, null, null, null, null);
		
		boolean eol = cs.moveToFirst();
		StringBuffer isbnList = new StringBuffer();
		while(eol) {
			isbnList.append("'" + cs.getString(6) + "',");
			eol = cs.moveToNext();
		}
		cs.close();
		db.close();
		if(isbnList.length() > 0) {
			isbnList.delete(isbnList.length()-1, isbnList.length());
			isbnList.insert(0, " and isbn not in (");
			isbnList.insert(isbnList.length(), ")");
		} else {
			isbnList.append("");
		}
		
		//ユーザー情報を取得
		SharedPreferences settings = getSharedPreferences(LoginActivity.PREFERENCES_FILE_NAME, Activity.MODE_PRIVATE);
		String loginId	= settings.getString("id", "");
		
		// ユーザーが借りている書籍を取得するSQL文を用意
		String sql = "select books.title as title, books.writer as writer, rental_date, return_plan_date, picture, isbn " +
						"from rentals, members, books " +
						"where rentals.member_id = members.id " +
						"and rentals.book_id = books.id " +
						"and user_id = '" + loginId + "'" +
						isbnList;
		
		//　StatementオブジェクトのexecuteQuery()メソッドでSQLを発行(検索)
		try {
			con = DBManager.getConnection();
			smt = con.createStatement();
			ResultSet rs = smt.executeQuery(sql);
			
			List<RentalList> list = new ArrayList<RentalList>();
			
			while(rs.next()){
				RentalList item = new RentalList();
				item.setTitle(rs.getString("title"));
				item.setWriter(rs.getString("writer"));
				item.setRentalDate(rs.getString("rental_date"));
				item.setReturnPlanDate(rs.getString("return_plan_date"));
				item.setPictureBmp(rs.getString("picture"), 50);
				item.setIsbn(rs.getString("isbn"));
				list.add(item);
			}
			rs.close();
			
			if(list.size() > 0) {
				rentalWrite(list);
			}
			
		} catch(SQLException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if(smt != null) {
				try {
					smt.close();
				} catch(SQLException ignore) {
					// none
				}
			}
			if(con != null) {
				try {
					con.close();
				} catch(SQLException ignore) {
					// none
				}
			}
		}
		
		// 日付を取得
		Calendar calendar = Calendar.getInstance();
		String time = String.format("%02d-%02d-%02d",
									calendar.get(Calendar.YEAR),
									calendar.get(Calendar.MONTH)+1,
									calendar.get(Calendar.DATE));
		SharedPreferences.Editor editor = settings.edit();
		editor.putString("lastUpDate", time.replaceAll("-", "/"));
		editor.commit();
        lastUpDate.setText(settings.getString("lastUpDate", ""));
	}
		
	public void rentalWrite(List<RentalList> list) {
		
		db = helper.getWritableDatabase();
		
		for(int i=0; i<list.size(); i++) {
			// 書籍情報をContentValuesに格納
			ContentValues cv = new ContentValues();
			
			cv.put("title", list.get(i).getTitle());
			cv.put("writer", list.get(i).getWriter());
			cv.put("rental_date", list.get(i).getRentalDate());
			cv.put("return_plan_date", list.get(i).getReturnPlanDate());
			list.get(i).setPictureByte(list.get(i).getPictureBmp());	// byte型の画像を設定
			cv.put("picture", list.get(i).getPictureByte());
			cv.put("isbn", list.get(i).getIsbn());
			
			//rentalsテーブル（アプリ）に登録
			db.insertWithOnConflict("rentals", null, cv, SQLiteDatabase.CONFLICT_REPLACE);	
		}
		db.close();
		listShow();
	}
	
}
